package com.credithc.customers.base.mapper;

import com.credithc.baseserv.core.mapper.BsBaseMapper;
import com.credithc.customers.base.po.ProductInfoStatisticPo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Title:
 * @Author: fby
 * @Date: 2019/5/5 15:09
 * @Description: com.credithc.customers.base.mapper
 * @Version: V1.0.0
 */
@Mapper
@Repository
public interface ProductInfoStatisticMapper extends BsBaseMapper<ProductInfoStatisticPo> {

    @Select("SELECT cpi.product_source_system as productSourceSystem,cpi.product_status as productStatus,product.product_type AS productType,#{yesterDay} AS date,count(1) AS sum,CURRENT_TIMESTAMP () AS createTime FROM cust_product_info cpi LEFT JOIN product_info product ON cpi.product_no = product.product_no and cpi.sub_system_sign = product.sub_system_sign WHERE cpi.product_start_time < #{date} GROUP BY cpi.product_source_system,cpi.product_status,product.product_type"
    )
    List<ProductInfoStatisticPo> getProductInit(@Param("date") String date, @Param("yesterDay") String yesterDay);

    @Select({"<script>",
            "SELECT SUM(sum) FROM",
            "product_info_statistic",
            "WHERE date = #{date}",
            "<when test='productStatus!=null'>",
            "AND product_status = #{productStatus}",
            "</when>",
            "<when test='productSourceSystem!=null'>",
            "AND product_source_system = #{productSourceSystem}",
            "</when>",
            "<when test='productType!=null'>",
            "AND product_type = #{productType}",
            "</when>",
            "</script>"})
    String getPastSum(@Param("productSourceSystem") String productSourceSystem, @Param("productType") String productType, @Param("productStatus") Integer productStatus, @Param("date") String date);

    @Select({"<script>",
            "SELECT count(1) FROM ",
            "cust_product_info p",
            "WHERE p.product_start_time BETWEEN #{endDate} AND #{nextDate}",
            "<when test='productStatus!=null'>",
            "AND p.product_status = #{productStatus}",
            "</when>",
            "<when test='productSourceSystem!=null'>",
            "AND p.product_source_system = #{productSourceSystem}",
            "</when>",
            "<when test='productType!=null'>",
            "AND product.product_type = #{productType}",
            "</when>",
            "</script>"})
    int getTodaySumByAll(@Param("productSourceSystem") String productSourceSystem, @Param("productType") String productType, @Param("productStatus") Integer productStatus, @Param("nextDate") String nextDate, @Param("endDate") String endDate);

    @Select("SELECT count(1) FROM cust_manage_info WHERE date = #{endDate} AND sub_system_sign = #{subSystemSign} ")
    int getTodaySumBySys(@Param("subSystemSign") String subSystemSign, @Param("endDate") String endDate);

    @Select("SELECT cpi.product_source_system as productSourceSystem,cpi.product_status as productStatus,product.product_type AS productType,'0000-00-00' AS date,count(1) AS sum,CURRENT_TIMESTAMP () AS createTime FROM cust_product_info cpi LEFT JOIN product_info product ON cpi.product_no = product.product_no AND cpi.sub_system_sign = product.sub_system_sign WHERE cpi.product_start_time IS NULL GROUP BY cpi.product_source_system,cpi.product_status,product.product_type"
    )
    List<ProductInfoStatisticPo> getProductInitTimeNull();

    @Select("SELECT cpi.product_source_system as productSourceSystem,cpi.product_status as productStatus,product.product_type AS productType,#{yesterDay} AS date,count(1) AS sum,CURRENT_TIMESTAMP () AS createTime FROM cust_product_info cpi LEFT JOIN product_info product ON cpi.product_no = product.product_no and cpi.sub_system_sign = product.sub_system_sign WHERE cpi.product_start_time < #{date} AND cpi.product_source_system = #{productSourceSystem} GROUP BY cpi.product_source_system,cpi.product_status,product.product_type"
    )
    List<ProductInfoStatisticPo> getProductInitBySys(@Param("date") String date, @Param("yesterDay") String yesterDay,@Param("productSourceSystem") String productSourceSystem);
}
